import random
from pymysql import Connection
import time

def get_regnum(count):
    regnum_len = 14
    zero = regnum_len - len(str(count))
    regnum = "2"
    while zero > 1:
        regnum += '0'
        zero -= 1
    regnum = regnum + str(count)
    return regnum

dwkhrq_year = ['2000', '2001', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009']
dwkhrq_month = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
dwkhrq_day = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
def get_regDate():
    year_index = random.randint(1, len(dwkhrq_year)-1)
    year = dwkhrq_year[year_index]
    month_index = random.randint(1, len(dwkhrq_month)-1)
    month = dwkhrq_month[month_index]
    day_index = random.randint(1, len(dwkhrq_day)-1)
    day = dwkhrq_day[day_index]
    return f'{year}-{month}-{day}'


def get_grzh_dwzh():
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='admin'
    )
    conn.select_db('excel')
    cursor = conn.cursor()
    cursor.execute(f'select dwzh, grzh from grxx')
    results: tuple = cursor.fetchall()
    result_dict = {}
    for result in results:
        result_dict[result[0]] = result[1]
    return result_dict


def get_jcje():
    first = random.randint(1, 9)
    sencond = random.randint(0, 9)
    three = random.randint(0, 9)
    four = random.randint(0, 9)
    return int(str(first) + str(sencond) + str(three) + str(four))


def get_regnum_count():
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='admin'
    )
    conn.select_db('excel')
    cursor = conn.cursor()
    cursor.execute("select count(1) as count from (select distinct regnum from grjcxx) t")
    results: tuple = cursor.fetchall()
    return results[0][0]

def get_jcxx_info(batch_size):
    init_count = get_regnum_count()
    count = init_count
    grzh_dwzh_dict = get_grzh_dwzh()
    grjcxx_info = 'insert into GRJCXX (REGNUM, REGDATE, DWZH, GRZH, JCJE, CREATEDUSER) VALUES '
    start_time = time.time()
    regnum_count = random.randint(1, 100)

    for grzh_dwzh in grzh_dwzh_dict:
        dwzh = grzh_dwzh
        grzh = grzh_dwzh_dict[dwzh]

        regnum = get_regnum(count)
        regdate = get_regDate()
        # 当regnum_count 为0时，则重新生成regnum的值，不为0时，持续使用该regnum和regdate
        if regnum_count == 0:
            count += 1
            regnum_count = random.randint(1, 100)
            regnum = get_regnum(count)
            regdate = get_regDate()

        regnum_count -= 1
        grjcxx_info += f"('{regnum}', '{regdate}', '{dwzh}', '{grzh}', {get_jcje()}, 'BATCH'),"

        # init_count记录已生成了多少条jcxx数据
        init_count += 1
        if init_count == batch_size:
            break
    grjcxx_info = grjcxx_info[0:len(grjcxx_info)-1:1]

    return grjcxx_info


def insert_grjcxx(grjcxx_insert_sql):
    conn = Connection(
        host='localhost',
        port=3306,
        user='root',
        password='admin',
        autocommit=True
    )
    conn.select_db('excel')
    cursor = conn.cursor()
    cursor.execute(grjcxx_insert_sql)
    cursor.fetchall()
    print("insert end")


if '__main__' == __name__:

    # print(get_regnum(0))
    # print(get_jcje())
    # result_dict = get_grzh_dwzh()
    # result_tuple =  result_dict.keys()
    # print(type(result_tuple))
    # result = result_tuple[0]
    # for result in result_dict:
    #     print(result)
    #     print(result_dict[result])
    # print(get_jcxx_info(1000))
    count = 0
    while count < 30:
        insert_grjcxx(get_jcxx_info(100000))
        count += 1

